-- 数据库迁移脚本 - 视频功能相关表
-- 创建时间：2024
-- 描述：添加视频相关表结构

-- 删除已存在的表（如果存在）
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE VIDEOS CASCADE CONSTRAINTS';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

-- 创建视频信息表
CREATE TABLE VIDEOS (
    VIDEOID NUMBER(10),
    TITLE NVARCHAR2(200) NOT NULL,
    DURATION NUMBER NOT NULL,  -- 视频时长（秒）
    VIDEOURL NVARCHAR2(500) NOT NULL,
    FILESIZE NUMBER,          -- 文件大小（字节）
    CREATEDDATE TIMESTAMP(7) DEFAULT SYSTIMESTAMP NOT NULL,
    UPDATEDDATE TIMESTAMP(7) DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT PK_VIDEOS PRIMARY KEY (VIDEOID)
);

-- 创建序列
CREATE SEQUENCE VIDEOS_SEQ START WITH 1 INCREMENT BY 1;

-- 创建触发器（用于自增主键）
CREATE OR REPLACE TRIGGER VIDEOS_TRG
BEFORE INSERT ON VIDEOS
FOR EACH ROW
BEGIN
    IF :NEW.VIDEOID IS NULL THEN
        SELECT VIDEOS_SEQ.NEXTVAL INTO :NEW.VIDEOID FROM DUAL;
    END IF;
END;
/

-- 创建更新时间触发器
CREATE OR REPLACE TRIGGER VIDEOS_UPD_TRG
BEFORE UPDATE ON VIDEOS
FOR EACH ROW
BEGIN
    :NEW.UPDATEDDATE := SYSTIMESTAMP;
END;
/

-- 创建索引
CREATE INDEX IDX_VIDEOS_TITLE ON VIDEOS (TITLE);
CREATE INDEX IDX_VIDEOS_CREATEDDATE ON VIDEOS (CREATEDDATE);

-- 授予必要的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON VIDEOS TO zlhis;
GRANT SELECT ON VIDEOS_SEQ TO zlhis;

-- 添加注释
COMMENT ON TABLE VIDEOS IS '视频信息表';
COMMENT ON COLUMN VIDEOS.VIDEOID IS '视频ID';
COMMENT ON COLUMN VIDEOS.TITLE IS '视频标题';
COMMENT ON COLUMN VIDEOS.DURATION IS '视频时长（秒）';
COMMENT ON COLUMN VIDEOS.VIDEOURL IS '视频URL地址';
COMMENT ON COLUMN VIDEOS.FILESIZE IS '文件大小（字节）';
COMMENT ON COLUMN VIDEOS.CREATEDDATE IS '创建时间';
COMMENT ON COLUMN VIDEOS.UPDATEDDATE IS '更新时间';

-- 提交事务
COMMIT;

-- 验证表创建
SELECT table_name FROM user_tables WHERE table_name = 'VIDEOS';